﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Text;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;

public partial class UpLoadFile : System.Web.UI.Page
{
    private static string filename = "";
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void AppendButton_Click(object sender, EventArgs e)
    {       
        if (FileUpload1.HasFile)
        {
            filename = FileUpload1.FileName;
            string filepath = Server.MapPath("Files") + "\\" + FileUpload1.FileName;
            FileInfo file = new FileInfo(filepath);
            if (file.Exists)
            {
                file.Delete();
            }           
                FileUpload1.SaveAs(filepath);
                Response.Write("<script language='javascript'>window.showModalDialog('AppendSortBook.aspx?filename=" +
                    HttpUtility.UrlEncode(FileUpload1.FileName) +
                    "&endlocation=" + HttpUtility.UrlEncode(txtRows.Text) + "')</script>");           
        }
    }
    protected void UpLoadButton_Click(object sender, EventArgs e)
    {
        string filepath = Server.MapPath("Files") + "\\" + FileUpload1.FileName;
        System.Data.DataTable dt = (System.Data.DataTable)Session["dt"];
        WriteFile(dt);
    }

    private void DoBind()
    {
 
    }

    private void WriteFile(System.Data.DataTable dt)
    {
        try
        {
            string filepath = Server.MapPath("Files") + "\\" + filename;
            Microsoft.Office.Interop.Excel.Application app = new Application();
            Workbook workbook = app.Workbooks.Add(filepath);
            Sheets sheets = app.Workbooks[1].Worksheets;
            _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                worksheet.Cells[i + 5, 1] = dt.Rows[i][0].ToString();
                worksheet.Cells[i + 5, 2] = dt.Rows[i][1].ToString();
                worksheet.Cells[i + 5, 3] = dt.Rows[i][2].ToString();
            }
            app.DisplayAlerts = false;
            char[] splitchar = new char[] { '.' };
            string name = filename.Split(splitchar)[0];
            filename = FileUpload1.FileName;
            FileInfo file = new FileInfo(filepath);
            file.Delete();
            string fullname = Server.MapPath("Files") + "\\" + name + "_" + ddlExpBoardVersion.SelectedItem.Text+".xls";
            workbook.SaveAs(fullname , XlFileFormat.xlExcel8, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
           // workbook.SaveAs(Server.MapPath("Files") + "\\" + name + "_" + ddlExpBoardVersion.SelectedItem.Text + ".xls");
            app.Quit();
            int generation = System.GC.GetGeneration(app);
            app = null;
            System.GC.Collect(generation);
            Response.Write("<script language='javascript'>alert('模板上传成功')</script>");
        }
        catch(Exception ee)
        {
            Response.Write("<script language='javascript'>alert('模板上传失败，错误："+ ee.Message +"')</script>");
        }
    }
}